using FarPoint.Win;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using GrapeCity.Spreadsheet;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Spread.Sparkline.Features
{
    public partial class ImageSparkline : Spread.Common.Features.Sample.BaseSampleControl
    {
        private object[,] _data;
        private readonly string _imagesDir = Path.Combine(Application.StartupPath, "Images");

        public ImageSparkline()
        {
            InitializeComponent();
            Name = "Image";
            InitData();
            InitSpread(_fpSpread);
        }

        #region Private Methods
        private void InitSpread(FpSpread fpSpread)
        {
            var sheet1 = fpSpread.Sheets[0];
            InitSpreadSheet(sheet1, ApplyImageFormula);
            var sheet2 = fpSpread.Sheets[1];
            InitSpreadSheet(sheet2, ApplyImageFormatter);
        }

        private void ApplyImageFormula(IWorksheet worksheet)
        {
            for (int i = 2; i < 17; i++)
            {
                worksheet.Cells[$"A{i + 1}"].Formula2 = $"GC.IMAGE(F{i + 1})";
            }
            worksheet.Columns["F"].ColumnWidth = 0;
        }

        private void ApplyImageFormatter(IWorksheet sheet)
        {
            for (int i = 2; i < 17; i++)
            {
                var cell = sheet.Cells[$"F{i + 1}"];
                cell.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;
                cell.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
                cell.CellType = new ImageCellType(RenderStyle.Normal);
                cell.Value = new Bitmap(cell.Text);
            }
        }

        private void InitSpreadSheet(SheetView sheetview, Action<IWorksheet> imageSetAction)
        {
            var worksheet = sheetview.AsWorksheet();
            worksheet.Cells["A1:F1"].Merge();

            worksheet.Cells["A1"].Value = "Employees List";
            worksheet.Cells["A1"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 15 });
            sheetview.Cells["A1"].BackColor = ColorTranslator.FromHtml("#E8E8E8");
            worksheet.Cells["A1"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;

            worksheet.Cells["A2:F2"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 13, Bold = true });
            worksheet.Cells["A2:F2"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["A2:F2"].Borders.ApplyBorder(new Border()
            {
                Bottom = new BorderLine(BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            });

            worksheet.SetValue(1, 1, _data);
            imageSetAction?.Invoke(worksheet);

            worksheet.Cells["E3:E17"].CellType = new DateTimeCellType()
            {
                DateTimeFormat = DateTimeFormat.UserDefined,
                UserDefinedFormat = "dd-MM-yyyy"
            };

            worksheet.Cells["A3:F17"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;

            for (int i = 1; i < 5; i++)
            {
                worksheet.Columns[$"{(char)(i + 65)}"].ColumnWidth = 160;
            }

            for (int i = 2; i < 17; i++)
            {
                worksheet.Rows[$"{i + 1}"].RowHeight = 55;
            }

            worksheet.Rows["1:2"].RowHeight = 30;
            worksheet.Columns["A"].ColumnWidth = 60;

            worksheet.Cells["H3:L3"].Merge();
            worksheet.Cells["H3"].Text = "You can set image according to URL or base64";
            worksheet.Cells["H3:L5"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["H3:L5"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;

            worksheet.Cells["H4:L5"].Merge();
            worksheet.Cells["H4"].Formula2 = $"GC.IMAGE(\"{_imagesDir}\\logo-en.ico\", 4, 50, 150, 1, 1, 1, 1, 1, 1)"; 
        }

        private void InitData()
        {
            _data = new object[,] {
                {"Employee Name","Department","Email","Start Date","PhotoUrl"},
                {"Thomas Miller","Legal","t.miller@company.com",DateTime.Parse("2020-06-11T00:00:00.000Z"), $"{_imagesDir}\\1.png"},
                {"Billy May", "R&D", "b.may@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\2.png"},
                { "John Watson", "Retail", "j.watson@company.com", DateTime.Parse("2020-01-30T00:00:00.000Z"), $"{_imagesDir}\\3.png"},
                { "Harry Tale", "HR", "h.tale@company.com", DateTime.Parse("2021-01-11T00:00:00.000Z"), $"{_imagesDir}\\4.png"},
                { "Bernard Martin", "R&D", "b.martin@company.com", DateTime.Parse("2020-01-13T00:00:00.000Z"), $"{_imagesDir}\\5.png"},
                { "Robin Caruso", "Sales", "r.caruso@company.com", DateTime.Parse("2021-02-11T00:00:00.000Z"), $"{_imagesDir}\\6.png"},
                { "David Willson", "HR", "d.willson@company.com", DateTime.Parse("2020-01-22T00:00:00.000Z"), $"{_imagesDir}\\7.png"},
                { "Stephan Willinghan", "HR", "s.willinghan@company.com", DateTime.Parse("2021-01-11T00:00:00.000Z"), $"{_imagesDir}\\8.png"},
                { "Anita Deville", "R&D", "a.deville@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\9.png"},
                { "Lana O'Connell", "Legal", "l.oconnell@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\10.png"},
                { "Adam Backer", "Finance", "a.backer@company.com", DateTime.Parse("2021-03-11T00:00:00.000Z"), $"{_imagesDir}\\11.png"},
                { "Ted Stanton", "Sales", "t.stanton@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\12.png"},
                { "Bryan Roberts", "HR", "b.roberts@company.com", DateTime.Parse("2020-11-16T00:00:00.000Z"), $"{_imagesDir}\\13.png"},
                { "Richard McLear", "Legal", "r.mclear@company.com", DateTime.Parse("2021-12-18T00:00:00.000Z"), $"{_imagesDir}\\14.png"},
                { "Jesse White", "Finance", "j.white@company.com", DateTime.Parse("2020-08-11T00:00:00.000Z"), $"{_imagesDir}\\15.png"}
            };
        }
        #endregion
    }
}


//
//ImageSparkline.Designer.cs
//



namespace Spread.Sparkline.Features
{
    partial class ImageSparkline
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(ImageSparkline));
            this._fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, ((object)(resources.GetObject("pnlMain.Controls"))));
            this._fpSpread_Sheet1 = this._fpSpread.GetSheet(0);
            this._fpSpread_Sheet2 = this._fpSpread.GetSheet(1);
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this._fpSpread);
            // 
            // _fpSpread
            // 
            this._fpSpread.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0";
            this._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this._fpSpread.Font = new System.Drawing.Font("Calibri", 11F);
            this._fpSpread.Location = new System.Drawing.Point(0, 0);
            this._fpSpread.Name = "_fpSpread";
            this._fpSpread.Size = new System.Drawing.Size(477, 487);
            this._fpSpread.TabIndex = 0;
            this._fpSpread.ActiveSheetIndex = 0;
            // 
            // ImageSparkline
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "ImageSparkline";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread _fpSpread;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet1;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet2;
    }
}
